A database is a collection of stored data that is logically related and that is accessible by one or more users or applications. A popular type of database is the relational database management system (RDBMS), which includes relational tables, also referred to as relations, made up of rows and columns (also referred to as tuples and attributes). Each row represents an occurrence of an entity defined by a table, with an entity being a person, place, thing, or other object about which the table contains information.
One of the goals of a database management system is to optimize the performance of queries for access and manipulation of data stored in the database. In massively parallel processing (MPP) systems, caching of database objects in memory is a well known technique for improving performance by reducing the need for disk input/output (JO). As central processing units (CPUs) have become more powerful at a rate that far out paces disk performance improvements, it is important to maximize the use of cache as a method for maintaining system balance. Cache memory for a database system is a precious resource. The price per byte of cache memory is often two orders of magnitude higher than that of disk space. Hence, it is not uncommon to find cache ratios on the order of 0.1% to 1% of customer data space on disk.
Database systems may use spool space to temporarily store intermediate results of, for example, SQL requests. Some database management systems provide workload management features that automatically recognize and respond to exceptions including those related to spool space. Although such automation is a great benefit to database administrators, actions are still substantially limited in the management and performance of spool cache reads and writes. Spool space usage is a direct result of the characteristics of the execution plans chosen by the Query Optimizer.
Disadvantageously, contemporary systems are incapable of directly involving the Query Optimizer in responding to runtime spool cache management. In fact, the Optimizer is wholly uninvolved in runtime spool space management in most systems.